package wang.xinqiu.mysqlcontrol.data

import android.util.Log
import kotlinx.coroutines.*
import kotlinx.coroutines.flow.MutableStateFlow
import kotlinx.coroutines.flow.StateFlow
import kotlinx.coroutines.flow.asStateFlow
import kotlinx.coroutines.sync.Mutex
import kotlinx.coroutines.sync.withLock
import java.sql.Connection
import java.sql.DriverManager
import java.sql.ResultSet

data class DatabaseInfo(
    val systemInfo: Map<String, String> = emptyMap(),
    val systemVariables: Map<String, Map<String, String>> = emptyMap(),
    val lastUpdated: Long = System.currentTimeMillis()
)

class MySQLManager {
    private val connectionPool = ConnectionPool()
    private var currentConnectionInfo: ConnectionInfo? = null
    private val connectionMutex = Mutex()
    
    // 会话管理
    private var sessionStartTime: Long = 0
    private var lastActivity: Long = 0
    private var lastActivityTime: Long = 0
    private val sessionTimeoutMinutes = 30
    
    // 数据库信息缓存
    private val _databaseInfoFlow = MutableStateFlow<DatabaseInfo?>(null)
    val databaseInfoFlow: StateFlow<DatabaseInfo?> = _databaseInfoFlow.asStateFlow()
    
    private var refreshJob: Job? = null
    private val refreshScope = CoroutineScope(Dispatchers.IO + SupervisorJob())
    
    private fun updateLastActivity() {
        lastActivity = System.currentTimeMillis()
        lastActivityTime = System.currentTimeMillis()
    }

    fun getSessionDurationMinutes(): Long {
        return if (sessionStartTime > 0) {
            (System.currentTimeMillis() - sessionStartTime) / 1000 / 60
        } else 0L
    }

    fun getSessionDurationSeconds(): Long {
        return if (sessionStartTime > 0) {
            (System.currentTimeMillis() - sessionStartTime) / 1000
        } else 0L
    }

    fun isSessionExpired(): Boolean {
        if (lastActivityTime == 0L) return false
        val inactiveMinutes = (System.currentTimeMillis() - lastActivityTime) / 1000 / 60
        return inactiveMinutes > sessionTimeoutMinutes
    }
    
    suspend fun connect(connectionInfo: ConnectionInfo): Result<String> {
        return withContext(Dispatchers.IO) {
            try {
                val connection = connectionPool.getConnection(connectionInfo)
                if (connection != null) {
                    currentConnectionInfo = connectionInfo
                    sessionStartTime = System.currentTimeMillis()
                    updateLastActivity()
                    
                    // 连接成功后立即获取数据库信息并开始自动刷新
                    startDatabaseInfoRefresh()
                    
                    Result.success("连接成功")
                } else {
                    Result.failure(Exception("无法建立数据库连接"))
                }
            } catch (e: Exception) {
                Result.failure(e)
            }
        }
    }
    
    private fun startDatabaseInfoRefresh() {
        // 取消之前的刷新任务
        refreshJob?.cancel()
        
        refreshJob = refreshScope.launch {
            // 立即获取一次数据库信息
            refreshDatabaseInfo()
            
            // 每10秒自动刷新
            while (isActive && isConnected()) {
                delay(10_000) // 10秒
                if (isConnected() && !isSessionExpired()) {
                    refreshDatabaseInfo()
                }
            }
        }
    }
    
    private suspend fun refreshDatabaseInfo() {
        try {
            val systemInfo = getSystemInfo()
            val systemVariables = getSystemVariables()
            
            val databaseInfo = DatabaseInfo(
                systemInfo = systemInfo,
                systemVariables = systemVariables,
                lastUpdated = System.currentTimeMillis()
            )
            
            _databaseInfoFlow.value = databaseInfo
            Log.d("MySQLManager", "数据库信息已更新")
        } catch (e: Exception) {
            Log.e("MySQLManager", "刷新数据库信息失败", e)
        }
    }
    
    private suspend fun getSystemInfo(): Map<String, String> {
        val systemInfoQueries = mapOf(
            "MySQL版本" to "SELECT VERSION()",
            "服务器启动时间" to "SELECT DATE_FORMAT(NOW() - INTERVAL VARIABLE_VALUE SECOND, '%Y-%m-%d %H:%i:%s') AS server_started FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Uptime'",
            "运行时长" to "SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Uptime'",
            "数据目录" to "SHOW VARIABLES LIKE 'datadir'",
            "操作系统" to "SHOW VARIABLES LIKE 'version_compile_os'",
            "编译架构" to "SHOW VARIABLES LIKE 'version_compile_machine'",
            "时区" to "SELECT @@time_zone",
            "总连接数" to "SHOW GLOBAL STATUS LIKE 'Connections'",
            "当前连接数" to "SHOW GLOBAL STATUS LIKE 'Threads_connected'",
            "新建数据库数量" to "SELECT COUNT(*) FROM information_schema.SCHEMATA WHERE SCHEMA_NAME NOT IN ('information_schema', 'performance_schema', 'mysql', 'sys')"
        )

        val systemInfoMap = mutableMapOf<String, String>()
        
        systemInfoQueries.forEach { (key, query) ->
            try {
                val result = executeQuery("information_schema", query)
                if (result.isSuccess) {
                    val value = result.getOrNull()?.rows?.firstOrNull()?.getOrNull(if (query.startsWith("SHOW")) 1 else 0)?.toString() ?: "未知"
                    systemInfoMap[key] = when (key) {
                        "运行时长" -> {
                            val seconds = value.toLongOrNull() ?: 0
                            formatUptime(seconds)
                        }
                        else -> if (key == "当前数据库" && value.isBlank()) "无" else value
                    }
                } else {
                    systemInfoMap[key] = "未知"
                }
            } catch (e: Exception) {
                systemInfoMap[key] = "查询失败"
                Log.e("MySQLManager", "查询系统信息失败: $key", e)
            }
        }
        
        return systemInfoMap
    }
    
    private suspend fun getSystemVariables(): Map<String, Map<String, String>> {
        val variableCategories = mapOf(
            "内存配置" to listOf(
                "innodb_buffer_pool_size", "tmp_table_size", "max_heap_table_size",
                "key_buffer_size", "innodb_log_file_size", "innodb_log_buffer_size", "sort_buffer_size",
                "read_buffer_size", "read_rnd_buffer_size", "join_buffer_size", "max_allowed_packet",
                "binlog_cache_size", "thread_stack"
            ),
            "连接配置" to listOf(
                "max_connections", "max_user_connections", "thread_cache_size", "back_log"
            ),
            "缓存配置" to listOf(
                "table_open_cache", "table_definition_cache"
            ),
            "超时配置" to listOf(
                "wait_timeout", "interactive_timeout", "connect_timeout", "net_read_timeout",
                "net_write_timeout", "lock_wait_timeout", "innodb_lock_wait_timeout"
            ),
            "InnoDB配置" to listOf(
                "innodb_flush_log_at_trx_commit", "innodb_file_per_table", "innodb_io_capacity",
                "innodb_read_io_threads", "innodb_write_io_threads", "innodb_thread_concurrency", "innodb_autoextend_increment"
            ),
            "日志配置" to listOf(
                "log_bin", "binlog_format", "sync_binlog", "expire_logs_days", "max_binlog_size",
                "binlog_cache_size", "binlog_stmt_cache_size", "slow_query_log", "long_query_time",
                "log_queries_not_using_indexes", "general_log", "log_error"
            ),
            "字符集和排序" to listOf(
                "character_set_server", "character_set_database", "character_set_client",
                "character_set_connection", "character_set_results", "collation_server",
                "collation_database", "collation_connection"
            ),
            "大小写和模式" to listOf(
                "lower_case_table_names", "lower_case_file_system", "sql_mode"
            ),
            "其他配置" to listOf(
                "default_storage_engine", "autocommit", "transaction_isolation",
                "max_prepared_stmt_count", "group_concat_max_len", "secure_file_priv", "local_infile"
            )
        )

        val systemVariablesByCategory = mutableMapOf<String, Map<String, String>>()

        variableCategories.forEach { (category, vars) ->
            val categoryVars = mutableMapOf<String, String>()
            vars.forEach { varName ->
                try {
                    val varResult = executeQuery("information_schema", "SHOW VARIABLES LIKE '$varName'")
                    if (varResult.isSuccess) {
                        val rows = varResult.getOrNull()?.rows
                        if (rows != null && rows.isNotEmpty()) {
                            val value = rows.firstOrNull()?.getOrNull(1) ?: "未知"
                            categoryVars[varName] = formatVariableValue(varName, value)
                        } else {
                            categoryVars[varName] = "未知"
                        }
                    } else {
                        categoryVars[varName] = "查询失败"
                    }
                } catch (e: Exception) {
                    categoryVars[varName] = "查询失败"
                    Log.e("MySQLManager", "查询变量失败: $varName", e)
                }
            }
            systemVariablesByCategory[category] = categoryVars
        }
        
        return systemVariablesByCategory
    }
    
    private fun formatUptime(seconds: Long): String {
        val days = seconds / 86400
        val hours = (seconds % 86400) / 3600
        val minutes = (seconds % 3600) / 60
        val remainingSeconds = seconds % 60
        
        val years = days / 365
        val remainingDaysAfterYears = days % 365
        val months = remainingDaysAfterYears / 30
        val remainingDays = remainingDaysAfterYears % 30
        
        return when {
            years > 0 -> {
                val parts = mutableListOf<String>()
                parts.add("${years}年")
                if (months > 0) parts.add("${months}个月")
                if (remainingDays > 0) parts.add("${remainingDays}天")
                if (hours > 0) parts.add("${hours}小时")
                if (minutes > 0) parts.add("${minutes}分钟")
                if (remainingSeconds > 0) parts.add("${remainingSeconds}秒")
                parts.joinToString("")
            }
            months > 0 -> {
                val parts = mutableListOf<String>()
                parts.add("${months}个月")
                if (remainingDays > 0) parts.add("${remainingDays}天")
                if (hours > 0) parts.add("${hours}小时")
                if (minutes > 0) parts.add("${minutes}分钟")
                if (remainingSeconds > 0) parts.add("${remainingSeconds}秒")
                parts.joinToString("")
            }
            days > 0 -> {
                val parts = mutableListOf<String>()
                parts.add("${days}天")
                if (hours > 0) parts.add("${hours}小时")
                if (minutes > 0) parts.add("${minutes}分钟")
                if (remainingSeconds > 0) parts.add("${remainingSeconds}秒")
                parts.joinToString("")
            }
            hours > 0 -> {
                val parts = mutableListOf<String>()
                parts.add("${hours}小时")
                if (minutes > 0) parts.add("${minutes}分钟")
                if (remainingSeconds > 0) parts.add("${remainingSeconds}秒")
                parts.joinToString("")
            }
            minutes > 0 -> {
                if (remainingSeconds > 0) {
                    "${minutes}分钟${remainingSeconds}秒"
                } else {
                    "${minutes}分钟"
                }
            }
            else -> "${remainingSeconds}秒"
        }
    }
    
    private fun formatVariableValue(varName: String, value: String): String {
        return when (varName) {
            "innodb_buffer_pool_size", "tmp_table_size",
            "max_heap_table_size", "key_buffer_size", "innodb_log_file_size",
            "innodb_log_buffer_size", "sort_buffer_size", "read_buffer_size",
            "read_rnd_buffer_size", "join_buffer_size", "max_allowed_packet",
            "binlog_cache_size", "thread_stack",
            "max_binlog_size", "group_concat_max_len" -> {
                formatMemorySize(value)
            }
            "wait_timeout", "interactive_timeout", "connect_timeout",
            "net_read_timeout", "net_write_timeout", "lock_wait_timeout",
            "innodb_lock_wait_timeout", "long_query_time" -> {
                val seconds = value.toDoubleOrNull() ?: 0.0
                if (seconds >= 60) {
                    "${seconds.toInt()}秒 (${(seconds/60).toInt()}分钟)"
                } else {
                    "${seconds}秒"
                }
            }
            "expire_logs_days" -> {
                val days = value.toLongOrNull() ?: 0
                if (days > 0) "${days}天" else value
            }
            "innodb_file_per_table", "autocommit", "slow_query_log",
            "log_queries_not_using_indexes", "local_infile" -> {
                when (value.uppercase()) {
                    "ON", "1" -> "开启"
                    "OFF", "0" -> "关闭"
                    else -> value
                }
            }
            "lower_case_table_names" -> {
                when (value) {
                    "0" -> "区分大小写"
                    "1" -> "不区分大小写(小写存储)"
                    "2" -> "不区分大小写(原样存储)"
                    else -> value
                }
            }
            else -> value
        }
    }
    
    private fun formatMemorySize(value: String): String {
        val bytes = value.toLongOrNull() ?: return value
        return when {
            bytes >= 1024 * 1024 * 1024 -> "${bytes / (1024 * 1024 * 1024)}GB"
            bytes >= 1024 * 1024 -> "${bytes / (1024 * 1024)}MB"
            bytes >= 1024 -> "${bytes / 1024}KB"
            else -> "${bytes}B"
        }
    }
    
    fun getCachedDatabaseInfo(): DatabaseInfo? {
        return _databaseInfoFlow.value
    }
    
    suspend fun testConnection(connectionInfo: ConnectionInfo): Result<String> {
        return withContext(Dispatchers.IO) {
            try {
                Class.forName("com.mysql.jdbc.Driver")
                val url = "jdbc:mysql://${connectionInfo.host}:${connectionInfo.port}/?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC"
                val connection: Connection = DriverManager.getConnection(
                    url,
                    connectionInfo.username,
                    connectionInfo.password
                )
                connection.close()
                Result.success("连接成功")
            } catch (e: Exception) {
                Result.failure(e)
            }
        }
    }
    
    fun getCurrentConnectionInfo(): ConnectionInfo? {
        return currentConnectionInfo
    }
    
    suspend fun isConnectionValid(): Boolean {
        return withContext(Dispatchers.IO) {
            try {
                currentConnectionInfo?.let { connInfo ->
                    val conn = connectionPool.getConnection(connInfo)
                    conn?.let {
                        if (it.isClosed) {
                            Log.d("MySQLManager", "连接已关闭")
                            return@withContext false
                        }
                        
                        val isValid = it.isValid(1)
                        Log.d("MySQLManager", "连接有效性检查结果: $isValid")
                        isValid
                    } ?: false
                } ?: false
            } catch (e: Exception) {
                Log.e("MySQLManager", "检查连接状态异常", e)
                false
            }
        }
    }
    
    suspend fun ensureConnectionValid(): Result<Unit> {
        return withContext(Dispatchers.IO) {
            try {
                connectionMutex.withLock {
                    if (!isConnectionValid()) {
                        Log.d("MySQLManager", "连接无效，尝试重新连接")
                        currentConnectionInfo?.let { connInfo ->
                            val reconnectResult = connect(connInfo)
                            if (reconnectResult.isSuccess) {
                                Log.d("MySQLManager", "重连成功")
                                delay(100)
                                Result.success(Unit)
                            } else {
                                Log.e("MySQLManager", "重连失败: ${reconnectResult.exceptionOrNull()?.message}")
                                Result.failure(reconnectResult.exceptionOrNull() ?: Exception("重连失败"))
                            }
                        } ?: Result.failure(Exception("无连接信息"))
                    } else {
                        Result.success(Unit)
                    }
                }
            } catch (e: Exception) {
                Log.e("MySQLManager", "确保连接有效时发生异常", e)
                Result.failure(e)
            }
        }
    }
    
    suspend fun listDatabases(): Result<List<String>> {
        return withContext(Dispatchers.IO) {
            try {
                val connectionCheck = ensureConnectionValid()
                if (connectionCheck.isFailure) {
                    return@withContext Result.failure(connectionCheck.exceptionOrNull()!!)
                }
                
                currentConnectionInfo?.let { connInfo ->
                    val connection = connectionPool.getConnection(connInfo)
                    connection?.let { conn ->
                        val statement = conn.createStatement()
                        val resultSet: ResultSet = statement.executeQuery("SHOW DATABASES")
                        
                        val databases = mutableListOf<String>()
                        while (resultSet.next()) {
                            databases.add(resultSet.getString(1))
                        }
                        
                        resultSet.close()
                        statement.close()
                        updateLastActivity()
                        
                        Result.success(databases)
                    } ?: Result.failure(Exception("连接不可用"))
                } ?: Result.failure(Exception("未建立连接"))
            } catch (e: Exception) {
                Result.failure(e)
            }
        }
    }
    
    suspend fun listTables(database: String): Result<List<String>> {
        return withContext(Dispatchers.IO) {
            try {
                val connectionCheck = ensureConnectionValid()
                if (connectionCheck.isFailure) {
                    return@withContext Result.failure(connectionCheck.exceptionOrNull()!!)
                }
                
                currentConnectionInfo?.let { connInfo ->
                    val connection = connectionPool.getConnection(connInfo)
                    connection?.let { conn ->
                        val statement = conn.createStatement()
                        statement.executeUpdate("USE `$database`")
                        val resultSet: ResultSet = statement.executeQuery("SHOW TABLES")
                        
                        val tables = mutableListOf<String>()
                        while (resultSet.next()) {
                            tables.add(resultSet.getString(1))
                        }
                        
                        resultSet.close()
                        statement.close()
                        updateLastActivity()
                        
                        Result.success(tables)
                    } ?: Result.failure(Exception("连接不可用"))
                } ?: Result.failure(Exception("未建立连接"))
            } catch (e: Exception) {
                Result.failure(e)
            }
        }
    }
    
    suspend fun executeQuery(databaseName: String, query: String): Result<QueryResult> {
        return withContext(Dispatchers.IO) {
            try {
                val connectionCheck = ensureConnectionValid()
                if (connectionCheck.isFailure) {
                    return@withContext Result.failure(connectionCheck.exceptionOrNull()!!)
                }
                
                currentConnectionInfo?.let { connInfo ->
                    val connection = connectionPool.getConnection(connInfo)
                    connection?.let { conn ->
                        conn.createStatement().use { statement ->
                            statement.executeUpdate("USE `$databaseName`")
                            
                            if (query.trim().uppercase().startsWith("SELECT") || 
                                query.trim().uppercase().startsWith("SHOW") ||
                                query.trim().uppercase().startsWith("DESCRIBE")) {
                                
                                val resultSet = statement.executeQuery(query)
                                val metaData = resultSet.metaData
                                val columnCount = metaData.columnCount
                                
                                val columns = mutableListOf<String>()
                                for (i in 1..columnCount) {
                                    columns.add(metaData.getColumnName(i))
                                }
                                
                                val rows = mutableListOf<List<String>>()
                                while (resultSet.next()) {
                                    val row = mutableListOf<String>()
                                    for (i in 1..columnCount) {
                                        row.add(resultSet.getString(i) ?: "")
                                    }
                                    rows.add(row)
                                }
                                
                                resultSet.close()
                                updateLastActivity()
                                Result.success(QueryResult(columns, rows, "查询成功，返回 ${rows.size} 行数据"))
                            } else {
                                val affectedRows = statement.executeUpdate(query)
                                updateLastActivity()
                                Result.success(QueryResult(
                                    columns = listOf("affected_rows"), 
                                    rows = listOf(listOf(affectedRows.toString())),
                                    message = "执行成功，影响 $affectedRows 行"
                                ))
                            }
                        }
                    } ?: Result.failure(Exception("连接不可用"))
                } ?: Result.failure(Exception("未建立连接"))
            } catch (e: Exception) {
                Result.failure(e)
            }
        }
    }
    
    suspend fun executeQueryWithSessionCheck(databaseName: String, query: String): Result<QueryResult> {
        return withContext(Dispatchers.IO) {
            try {
                if (isSessionExpired()) {
                    return@withContext Result.failure(Exception("会话已过期，请重新连接"))
                }
                
                updateLastActivity()
                executeQuery(databaseName, query)
            } catch (e: Exception) {
                Result.failure(e)
            }
        }
    }
    
    suspend fun disconnect() {
        withContext(Dispatchers.IO) {
            // 停止自动刷新
            refreshJob?.cancel()
            refreshJob = null
            
            connectionPool.closeAll()
            currentConnectionInfo = null
            lastActivity = 0
            sessionStartTime = 0
            lastActivityTime = 0
            
            // 清空缓存
            _databaseInfoFlow.value = null
        }
    }
    
    fun isConnected(): Boolean {
        return currentConnectionInfo != null && connectionPool.getConnectionCount() > 0
    }
    
    suspend fun getConnectionPoolStats(): String {
        return "活跃连接数: ${connectionPool.getConnectionCount()}"
    }

    suspend fun extendSession(): Result<String> {
        return withContext(Dispatchers.IO) {
            try {
                currentConnectionInfo?.let { connInfo ->
                    val connection = connectionPool.getConnection(connInfo)
                    connection?.let { conn ->
                        val stmt = conn.createStatement()
                        stmt.executeQuery("SELECT 1").close()
                        stmt.close()
                        updateLastActivity()
                        Result.success("会话已延长")
                    } ?: Result.failure(Exception("连接不存在"))
                } ?: Result.failure(Exception("连接不存在"))
            } catch (e: Exception) {
                Result.failure(e)
            }
        }
    }

    suspend fun pingConnection(): Result<Boolean> {
        return withContext(Dispatchers.IO) {
            try {
                currentConnectionInfo?.let { connInfo ->
                    val connection = connectionPool.getConnection(connInfo)
                    val isValid = connection?.isValid(3) == true
                    if (isValid) updateLastActivity()
                    Result.success(isValid)
                } ?: Result.success(false)
            } catch (e: Exception) {
                Result.failure(e)
            }
        }
    }
}